﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using HotelSoft.HotelManagement_DTO;
using System.Collections;

namespace HotelSoft.HotelManagement_DAO
{
    class BaoCaoDoanhThuDAO
    {
        public ArrayList BaoCaoDoanhThuNgay(DateTime tNgay)
        {
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand ="SELECT pdk.SoPhong,p.TenLP,kh.HoTenKH,kh.GioiTinh,kh.QuocTich,pdk.SoNgayThue,lp.Gia,hd.TongTien,hd.NgayThanhToan"
                                + " FROM KHACHHANG kh,KHACHHANG_PHIEUDANGKY khpdk,PHIEUDANGKY pdk,HOADON_THANHTOAN hd, LOAIPHONG lp, PHONG p "
                                + "WHERE kh.CMND=khpdk.CMND and khpdk.SoPDK = pdk.SoPDK and pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP and pdk.SoPDK = hd.SoPDK"
            + " and year(hd.NgayThanhToan) = " + tNgay.Year + " and month(hd.NgayThanhToan) = " + tNgay.Month + " and day(hd.NgayThanhToan) = " + tNgay.Day;
         
            DataTable ds = Config.AccessHelper.ExecuteQuery(searchCommand);
            BaoCaoDoanhThuDTO BaoCao;

            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                BaoCao = new BaoCaoDoanhThuDTO();
                BaoCao.m_SoPhong = ds.Rows[i]["SoPhong"].ToString();
                BaoCao.m_TenPhong = ds.Rows[i]["TenLP"].ToString();
                BaoCao.m_TenKH = ds.Rows[i]["HoTenKH"].ToString();
                BaoCao.m_GioiTinh = ds.Rows[i]["GioiTinh"].ToString();
                BaoCao.m_QuocTich = ds.Rows[i]["QuocTich"].ToString();
                BaoCao.m_SoNgayO = int.Parse(ds.Rows[i]["SoNgayThue"].ToString());
                BaoCao.m_DonGia = float.Parse(ds.Rows[i]["Gia"].ToString());
                BaoCao.m_SoTien = float.Parse(ds.Rows[i]["TongTien"].ToString());
                BaoCao.m_NgayThanhToan = DateTime.Parse(ds.Rows[i]["NgayThanhToan"].ToString());
                arrDanhSach.Add(BaoCao);
                i++;
            }
            return arrDanhSach;
        }

        public ArrayList BaoCaoDoanhThuThang(DateTime tNgay)
        {
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand = "SELECT pdk.SoPhong,p.TenLP,kh.HoTenKH,kh.GioiTinh,kh.QuocTich,pdk.SoNgayThue,lp.Gia,hd.TongTien,hd.NgayThanhToan"
                                + " FROM KHACHHANG kh,KHACHHANG_PHIEUDANGKY khpdk,PHIEUDANGKY pdk,HOADON_THANHTOAN hd, LOAIPHONG lp, PHONG p "
                                + "WHERE kh.CMND=khpdk.CMND and khpdk.SoPDK = pdk.SoPDK and pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP and pdk.SoPDK = hd.SoPDK"
            + " and year(hd.NgayThanhToan) = " + tNgay.Year + " and month(hd.NgayThanhToan) = " + tNgay.Month;

            DataTable ds = Config.AccessHelper.ExecuteQuery(searchCommand);
            BaoCaoDoanhThuDTO BaoCao;

            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                BaoCao = new BaoCaoDoanhThuDTO();
                BaoCao.m_SoPhong = ds.Rows[i]["SoPhong"].ToString();
                BaoCao.m_TenPhong = ds.Rows[i]["TenLP"].ToString();
                BaoCao.m_TenKH = ds.Rows[i]["HoTenKH"].ToString();
                BaoCao.m_GioiTinh = ds.Rows[i]["GioiTinh"].ToString();
                BaoCao.m_QuocTich = ds.Rows[i]["QuocTich"].ToString();
                BaoCao.m_SoNgayO = int.Parse(ds.Rows[i]["SoNgayThue"].ToString());
                BaoCao.m_DonGia = float.Parse(ds.Rows[i]["Gia"].ToString());
                BaoCao.m_SoTien = float.Parse(ds.Rows[i]["TongTien"].ToString());
                BaoCao.m_NgayThanhToan = DateTime.Parse(ds.Rows[i]["NgayThanhToan"].ToString());
                arrDanhSach.Add(BaoCao);
                i++;
            }
            return arrDanhSach;
        }

        public ArrayList BaoCaoDoanhThuNam(DateTime tNgay)
        {
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand = "SELECT pdk.SoPhong,p.TenLP,kh.HoTenKH,kh.GioiTinh,kh.QuocTich,pdk.SoNgayThue,lp.Gia,hd.TongTien,hd.NgayThanhToan"
                                + " FROM KHACHHANG kh,KHACHHANG_PHIEUDANGKY khpdk,PHIEUDANGKY pdk,HOADON_THANHTOAN hd, LOAIPHONG lp, PHONG p "
                                + "WHERE kh.CMND=khpdk.CMND and khpdk.SoPDK = pdk.SoPDK and pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP and pdk.SoPDK = hd.SoPDK"
            + " and year(hd.NgayThanhToan) = " + tNgay.Year;

            DataTable ds = Config.AccessHelper.ExecuteQuery(searchCommand);
            BaoCaoDoanhThuDTO BaoCao;

            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                BaoCao = new BaoCaoDoanhThuDTO();
                BaoCao.m_SoPhong = ds.Rows[i]["SoPhong"].ToString();
                BaoCao.m_TenPhong = ds.Rows[i]["TenLP"].ToString();
                BaoCao.m_TenKH = ds.Rows[i]["HoTenKH"].ToString();
                BaoCao.m_GioiTinh = ds.Rows[i]["GioiTinh"].ToString();
                BaoCao.m_QuocTich = ds.Rows[i]["QuocTich"].ToString();
                BaoCao.m_SoNgayO = int.Parse(ds.Rows[i]["SoNgayThue"].ToString());
                BaoCao.m_DonGia = float.Parse(ds.Rows[i]["Gia"].ToString());
                BaoCao.m_SoTien = float.Parse(ds.Rows[i]["TongTien"].ToString());
                BaoCao.m_NgayThanhToan = DateTime.Parse(ds.Rows[i]["NgayThanhToan"].ToString());
                arrDanhSach.Add(BaoCao);
                i++;
            }
            return arrDanhSach;
        }
    }
}
